﻿using MyLogPC.Model;
using MyLogPC.Logging;
using MyLogPC.Config;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Diagnostics;

namespace MyLogPC.SQLs
{
    internal class DbHelper
    {
        private static readonly object _lockObject = new object();
        private static readonly LogManager _logger = LogManager.Instance;
        private static AppConfig _config = ConfigManager.GetConfig();
        public static ConnectionConfig _connectionConfig;
        private static ConnectionConfig _addressConnectionConfig;

        static DbHelper()
        {
            InitializeConnectionConfigs();
        }

        /// <summary>
        /// 初始化数据库连接配置
        /// </summary>
        private static void InitializeConnectionConfigs()
        {
            _connectionConfig = new ConnectionConfig
            {
                ConnectionString = _config.Database.DataConnectionString,
                DbType = SqlSugar.DbType.Sqlite,
                IsAutoCloseConnection = true,
                InitKeyType = InitKeyType.Attribute
            };

            _addressConnectionConfig = new ConnectionConfig
            {
                ConnectionString = _config.Database.AddressConnectionString,
                DbType = SqlSugar.DbType.Sqlite,
                IsAutoCloseConnection = true,
                InitKeyType = InitKeyType.Attribute
            };
        }

        /// <summary>
        /// 重新加载配置
        /// </summary>
        public static void ReloadConfig()
        {
            _config = ConfigManager.GetConfig();
            InitializeConnectionConfigs();
            _logger.Info("数据库配置已重新加载", "DbHelper");
        }

        /// <summary>
        /// 检查并创建数据库
        /// </summary>
        public static void CheckDb()
        {
            try
            {
                lock (_lockObject)
                {
                    // 检查主数据库
                    using (var db = new SqlSugarClient(_connectionConfig))
                    {
                        if (!System.IO.File.Exists("data.db"))
                        {
                            var result = db.DbMaintenance.CreateDatabase();
                            _logger.Info("主数据库创建成功", "DbHelper");
                        }
                    }

                    // 检查地址数据库
                    using (var db = new SqlSugarClient(_addressConnectionConfig))
                    {
                        if (!System.IO.File.Exists("Address.db"))
                        {
                            var result = db.DbMaintenance.CreateDatabase();
                            _logger.Info("地址数据库创建成功", "DbHelper");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                _logger.Error("检查数据库时发生错误", ex, "DbHelper");
                throw;
            }
        }

        /// <summary>
        /// 添加数据到数据表（异步版本）
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dataModel"></param>
        public static async Task AddPlcDataToSqlAsync<T>(T dataModel) where T : BasePlcDataModel, new()
        {
            if (dataModel == null)
            {
                _logger.Warning("尝试插入空数据模型", "DbHelper");
                return;
            }

            try
            {
                using (var db = new SqlSugarClient(_connectionConfig))
                {
                    await db.Insertable(dataModel).SplitTable().ExecuteReturnSnowflakeIdListAsync();
                }
            }
            catch (Exception ex)
            {
                _logger.Error($"插入PLC数据时发生错误，数据类型：{typeof(T).Name}", ex, "DbHelper");
            }
        }

        /// <summary>
        /// 添加数据到数据表（同步版本，保持向后兼容）
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dataModel"></param>
        public static void AddPlcDataToSql<T>(T dataModel) where T : BasePlcDataModel, new()
        {
            if (dataModel == null)
            {
                _logger.Warning("尝试插入空数据模型", "DbHelper");
                return;
            }

            try
            {
                using (var db = new SqlSugarClient(_connectionConfig))
                {
                    db.Insertable(dataModel).SplitTable().ExecuteReturnSnowflakeIdList();
                }
            }
            catch (Exception ex)
            {
                _logger.Error($"插入PLC数据时发生错误，数据类型：{typeof(T).Name}", ex, "DbHelper");
                // 不重新抛出异常，避免中断数据采集
            }
        }

        /// <summary>
        /// 批量添加数据到数据表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dataModels"></param>
        public static async Task AddPlcDataBatchAsync<T>(List<T> dataModels) where T : BasePlcDataModel, new()
        {
            if (dataModels == null || !dataModels.Any())
            {
                return;
            }

            try
            {
                using (var db = new SqlSugarClient(_connectionConfig))
                {
                    await db.Insertable(dataModels).SplitTable().ExecuteReturnSnowflakeIdListAsync();
                }
                _logger.Info($"批量插入 {dataModels.Count} 条 {typeof(T).Name} 数据成功", "DbHelper");
            }
            catch (Exception ex)
            {
                _logger.Error($"批量插入PLC数据时发生错误，数据类型：{typeof(T).Name}，数量：{dataModels.Count}", ex, "DbHelper");
            }
        }

        /// <summary>
        /// 地址查询泛型方法（异步版本）
        /// </summary>
        public static async Task<List<T>> GetAddressesAsync<T>() where T : BaseAddressModel, new()
        {
            try
            {
                using (var db = new SqlSugarClient(_addressConnectionConfig))
                {
                    //按id排序
                    var data = await db.Queryable<T>().OrderBy((st) => st.Id).ToListAsync();

                    _logger.Debug($"查询到 {data.Count} 条 {typeof(T).Name} 地址数据", "DbHelper");
                    return data;
                }
            }
            catch (Exception ex)
            {
                _logger.Error($"查询地址数据时发生错误，数据类型：{typeof(T).Name}", ex, "DbHelper");
                return new List<T>();
            }
        }

        /// <summary>
        /// 地址查询泛型方法（同步版本，保持向后兼容）
        /// </summary>
        public static List<T> GetAddresses<T>() where T : BaseAddressModel, new()
        {
            try
            {
                using (var db = new SqlSugarClient(_addressConnectionConfig))
                {
                    var data = db.Queryable<T>().ToList();
                    _logger.Debug($"查询到 {data.Count} 条 {typeof(T).Name} 地址数据", "DbHelper");
                    return data;
                }
            }
            catch (Exception ex)
            {
                _logger.Error($"查询地址数据时发生错误，数据类型：{typeof(T).Name}", ex, "DbHelper");
                return new List<T>();
            }
        }

        /// <summary>
        /// 获取历史数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="address"></param>
        /// <returns></returns>
        public static async Task<List<T>> GetHistoryDataAsync<T>(DateTime startTime, DateTime endTime, string? address = null) 
            where T : BasePlcDataModel, new()
        {
            try
            {
                using (var db = new SqlSugarClient(_connectionConfig))
                {
                    var query = db.Queryable<T>()
                        .SplitTable(startTime, endTime)
                        .Where(x => x.SamplingTime >= startTime && x.SamplingTime <= endTime);

                    if (!string.IsNullOrEmpty(address))
                    {
                        query = query.Where(x => x.Address == address);
                    }

                    var data = await query.ToListAsync();
                    _logger.Info($"查询历史数据成功，类型：{typeof(T).Name}，数量：{data.Count}", "DbHelper");
                    return data;
                }
            }
            catch (Exception ex)
            {
                _logger.Error($"查询历史数据时发生错误，数据类型：{typeof(T).Name}", ex, "DbHelper");
                return new List<T>();
            }
        }

        /// <summary>
        /// 清理过期数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="retentionDays">保留天数</param>
        /// <returns></returns>
        public static async Task<int> CleanExpiredDataAsync<T>(int retentionDays) where T : BasePlcDataModel, new()
        {
            try
            {
                var cutoffDate = DateTime.Now.AddDays(-retentionDays);
                using (var db = new SqlSugarClient(_connectionConfig))
                {
                    // 修复删除语法，使用SQL直接删除
                    var tableName = db.EntityMaintenance.GetTableName<T>();
                    var sql = $"DELETE FROM {tableName} WHERE SamplingTime < @cutoffDate";
                    var deletedCount = await db.Ado.ExecuteCommandAsync(sql, new { cutoffDate });
                    
                    _logger.Info($"清理过期数据成功，类型：{typeof(T).Name}，删除数量：{deletedCount}", "DbHelper");
                    return deletedCount;
                }
            }
            catch (Exception ex)
            {
                _logger.Error($"清理过期数据时发生错误，数据类型：{typeof(T).Name}", ex, "DbHelper");
                return 0;
            }
        }

        /// <summary>
        /// 获取数据统计信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="days">统计天数</param>
        /// <returns></returns>
        public static async Task<Dictionary<string, object>> GetDataStatisticsAsync<T>(int days = 7) where T : BasePlcDataModel, new()
        {
            try
            {
                var startTime = DateTime.Now.AddDays(-days);
                var endTime = DateTime.Now;

                using (var db = new SqlSugarClient(_connectionConfig))
                {
                    var totalCount = await db.Queryable<T>()
                        .SplitTable(startTime, endTime)
                        .Where(x => x.SamplingTime >= startTime && x.SamplingTime <= endTime)
                        .CountAsync();

                    var todayCount = await db.Queryable<T>()
                        .SplitTable(DateTime.Today, DateTime.Now)
                        .Where(x => x.SamplingTime >= DateTime.Today)
                        .CountAsync();

                    var statistics = new Dictionary<string, object>
                    {
                        ["DataType"] = typeof(T).Name,
                        ["TotalCount"] = totalCount,
                        ["TodayCount"] = todayCount,
                        ["StatisticsDays"] = days,
                        ["StartTime"] = startTime,
                        ["EndTime"] = endTime
                    };

                    _logger.Debug($"获取数据统计信息成功，类型：{typeof(T).Name}，总数：{totalCount}，今日：{todayCount}", "DbHelper");
                    return statistics;
                }
            }
            catch (Exception ex)
            {
                _logger.Error($"获取数据统计信息时发生错误，数据类型：{typeof(T).Name}", ex, "DbHelper");
                return new Dictionary<string, object>();
            }
        }

        #region 查询
        public static async Task<List<T>> QueryAsync<T>(string sql, object? parameters = null) where T : class, new()
        {
            try
            {
                using (var db = new SqlSugarClient(_connectionConfig))
                {
                    var data = await db.Ado.SqlQueryAsync<T>(sql, parameters);
                    _logger.Debug($"执行查询成功，SQL：{sql}", "DbHelper");
                    return data;
                }
            }
            catch (Exception ex)
            {
                _logger.Error($"执行查询时发生错误，SQL：{sql}", ex, "DbHelper");
                return new List<T>();
            }
        }
        /// <summary>
        /// 查询报警信息
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public static async Task<List<AlarmModel>> QueryAlarmsAsync(DateTime startTime, DateTime endTime)
        {
            try
            {
                using (var db = new SqlSugarClient(_connectionConfig))
                {
                    var data = await db.Queryable<AlarmModel>()
                        .SplitTable()
                        .Where(x => x.SamplingTime >= startTime && x.SamplingTime <= endTime)
                        .OrderBy(x => x.SamplingTime)
                        .ToListAsync();
                    _logger.Debug($"查询报警信息成功，数量：{data.Count}", "DbHelper");
                    return data;
                }
            }
            catch (Exception ex)
            {
                _logger.Error("查询报警信息时发生错误", ex, "DbHelper");
                return new List<AlarmModel>();
            }
        }
        #endregion



    }
}
